package com.yilifang.dao.cluster;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * Created by zhuan on 2018-09-14.
 */
@Mapper
public interface ClusterDirectSqlMapper {

    @Select("SELECT *\n" +
            "FROM (SELECT tt.*, ROWNUM AS rowno \n" +
            "\t  FROM (   \tselect \n" +
            "\t\t\t\tt.title NewsTitle,\n" +
            "\t\t\t\tb.content NewsContent,\n" +
            "\t\t\t\tt.create_date ReleaseDate,\n" +
            "\t\t\t\tt.READ_COUNT ReadCount,\n" +
            "\t\t\t\t(select name from org_unit where id = t.PUBLISH_DEPARTMENT_ID ) ReleaseDept,\n" +
            "\t\t\t\t(select name from org_member where id = t.PUBLISH_USER_ID) ReleaseUser\n" +
            "\t\t\t\tfrom NEWS_DATA t\n" +
            "\t\t\t\tinner join NEWS_BODY b\n" +
            "\t\t\t\ton b.id = t.id\n" +
            "\t\t\t\twhere  DELETED_FLAG = 0\n" +
            "\t\t\t\torder by t.create_date desc) tt \n" +
            "\t WHERE ROWNUM <= #{page}*#{size}) table_alias \n" +
            "WHERE table_alias.rowno >= (#{page}-1)*#{size}")
    List<Map<String, Object>> selectOANews(@Param("page") Integer page, @Param("size") Integer size);


    @Select("SELECT *\n" +
            "FROM (SELECT tt.*, ROWNUM AS rowno \n" +
            "\t  FROM (   \tselect \n" +
            "\t\t\t\tt.title NewsTitle,\n" +
            "\t\t\t\tto_char(t.id) id,\n" +
            "\t\t\t\tt.create_date ReleaseDate,\n" +
            "\t\t\t\tt.READ_COUNT ReadCount,\n" +
            "\t\t\t\t(select name from org_unit where id = t.PUBLISH_DEPARTMENT_ID ) ReleaseDept,\n" +
            "\t\t\t\t(select name from org_member where id = t.PUBLISH_USER_ID) ReleaseUser\n" +
            "\t\t\t\tfrom NEWS_DATA t\n" +
            "\t\t\t\twhere  DELETED_FLAG = 0\n" +
            "\t\t\t\torder by t.create_date desc) tt \n" +
            "\t WHERE ROWNUM <= #{page}*#{size}) table_alias \n" +
            "WHERE table_alias.rowno >= (#{page}-1)*#{size}")
    List<Map<String, Object>> selectOANewsList(@Param("page") Integer page, @Param("size") Integer size);

    @Select("select \n" +
            "t.title NewsTitle, \n" +
            "b.content NewsContent,\n" +
            "to_char(t.id) id, \n" +
            "t.create_date ReleaseDate, \n" +
            "t.READ_COUNT ReadCount, \n" +
            "(select name from org_unit where id = t.PUBLISH_DEPARTMENT_ID ) ReleaseDept, \n" +
            "(select name from org_member where id = t.PUBLISH_USER_ID) ReleaseUser \n" +
            "from NEWS_DATA t \n" +
            "inner join NEWS_BODY b\n" +
            "on b.id = t.id\n" +
            "where  DELETED_FLAG = 0 and t.id = #{id}")
    Map<String, Object> selectOANewsBodyById(@Param("id") String id);

    @Select("select count(*) newsCount from NEWS_DATA where  DELETED_FLAG = 0")
    Map<String, Object> selectOaNewsCount();
}
